package com.hnjowe.webdb;

import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;

/**
 * 数据库操作封装类：PreparedStatement的封装
 * 用于操作预存储SQL语句操作
 * @author 王秀武
 * @version 1.0
 */
public class DBOperation {
    private Connection conn;
    private PreparedStatement pres;
    private static Logger log = Logger.getLogger(DBOperation.class.getName());

    /**
     * 构造方法返回连接数据库
     */
    public  DBOperation() {
    	DBConnection dbConnection = new DBConnection();
        try {
            conn = dbConnection.getConnection();
        } catch (Exception ex){
        	log.error("can not get connection from database server while connetion to database"+ex);
        }
    }

    /**
     * 连接事物开始
     * @return result
     */
    public boolean transcationBegin() {
        boolean result =false;
        try {
            if (conn != null) {
                conn.setAutoCommit(false);
                result = true;
            } else {
            	log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            log.error("transcation set auto commit true error:"+ex);
        }
        return result;
    }


    /**
     * 事物结束
     * @return result
     */
    public boolean transcationEnd(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                result = true;
            } else {
            	log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            log.error("transcation set auto commit true error:"+ex);
        }
        return result;
    }

    /**
     * 事物提交
     * @return result
     */
    public boolean transcationCommit(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.commit();
                result = true;
            } else {
            	log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            log.error("transcation commit error:"+ex);
        }
        return result;
    }

    /**
     * 事物回退
     * @return result
     */
    public boolean transcationRollback(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.rollback();
                result = true;
            } else {
            	log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            log.error("transcation rollback error:"+ex);
        }
        return result;
    }

    public void setLong(int index,long value) {
        try {
            if (pres != null){
                pres.setLong(index,value);
            }else {
            	log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setInt(int index,int value) {
        try {
            if (pres != null){
                pres.setInt(index,value);
            }else {
            	log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setFloat(int index,float value) {
        try {
            if (pres != null){
                pres.setFloat(index,value);
            }else {
                log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setDouble(int index,double value) {
        try {
            if (pres != null){
                pres.setDouble(index,value);
            }else {
            	log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setString(int index,String value) {
        try {
            if (pres != null){
                pres.setString(index,value);
            }else {
            	log.info("can not get preparestatment from connection");
            }
        }catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setNull(int index,int value) {
        try {
            if (pres != null){
                pres.setNull(index,value);
            }else {
            	log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
        	//log.error("can not get pres from connection"+ex);
        }
    }

    public void setTimestamp(int index,Timestamp value) {
        try {
            if (pres != null){
                pres.setTimestamp(index,value);
            }else {
                log.info("can not get preparestatment from connection");
            }
        } catch(SQLException ex) {
            //log.error("can not get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value Date
     */
    public void setDate(int index, Date value) {
        try {
            if (pres != null) {
                pres.setDate(index, value);
            }
            else {
                log.info("can not get preparestatment from connection");
            }
        }
        catch (SQLException ex) {
            //log.error("can not get pres from connection"+ex);
        }
    }

    /**
     * 获得一个PrepStatment对象
     * @param updateSql String
     */
    public void prepStatment(String updateSql) {
        try {
            if (conn != null){
                pres = conn.prepareStatement(updateSql);
            }else {
                log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            //log.error("execute update string error:"+ex);
        }
    }

    /**
     * 获得一个返回自动生成的主键prepareStatement对象
     * @param updateSql
     */
    public void prepStatmentRtnKey(String updateSql) {
        try {
            if (conn != null){
                pres = conn.prepareStatement(updateSql, Statement.RETURN_GENERATED_KEYS);
            }else {
                log.info("can not get connection from database server");
            }
        } catch(SQLException ex) {
            log.error("execute update string error:"+ex);
        }
    }

    /**
     * 提交查询
     * @return res
     */
    public ResultSet executeQuery() {
        ResultSet res = null;
        try {
            if (pres != null) {
                //pres = conn.prepareStatement(querySql);
                res  = pres.executeQuery();
            }else {
                log.info("can not get connection from database server");
            }
        } catch (SQLException ex) {
            //log.error("execute query string error:"+ex);
        }
        return res;
    }

    /**
     * 提交修改
     * @return result
     */
    public int executeUpdate(){
        int result = -1;
        try {
            if (pres != null){
                //System.out.println("Insert seccess!");//test case!
                result = pres.executeUpdate();
                if(result==-1)
                    System.out.println("haha");//test case!
            }else {
                log.info("can not get prepareStament from database server");
            }
            //closeConn();
        } catch(SQLException ex) {
            result = -1;
            //log.error("execute update string error:"+ex);
        }
        //System.out.println(result);
        return result;
    }

    public boolean execute(){
        boolean result = false;
        try {
            if (pres != null){
                //System.out.println("Insert seccess!");//test case!
                result = pres.execute();
                //if(result==false)
                    //System.out.println("haha");//test case!
            }else {
                log.info("can not get prepareStament from database server");
            }
            //closeConn();
        } catch(SQLException ex) {
            result = false;
            //log.error("execute update string error:"+ex);
        }
        //System.out.println(result);
        return result;
    }

    /**
     * 关闭连接
     */
    public void closeConn() {
        try {
            if ( conn != null && conn.isClosed()==false)
                conn.close();
        } catch(SQLException ex) {
            log.error("can not close connection error:"+ex);
        }
    }

    /**
     * 关闭PrepStatment，在关闭连接之前使用
     */
    public void closePres() {
        try {
            if ( pres != null)
                pres.close();
        } catch(SQLException ex) {
            log.error("can not close connection error:"+ex);
        }
    }

	public PreparedStatement getPres() {
		return pres;
	}
}